﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ISS.IR.RRNMS.Library.DataAccess
{
    public static partial class Main
    {
        public static List<Item> GetCategoryItems(int categoryId)
        {
            try
            {
                command.CommandText = "SelectItemsByCategory";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("@categoryId", categoryId));

                DataTable dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                if (dtMaster != null && dtMaster.Rows.Count > 0)
                {
                    List<Item> items = new List<Item>();

                    foreach (DataRow dr in dtMaster.Rows)
                    {
                        Item item = new Item();

                        item.Id = Convert.ToInt32(dr["Id"]);
                        item.Value = dr["Value"].ToString();

                        items.Add(item);
                    }

                    return items;
                }

                else
                    return null;
            }
            catch (SqlException sqlExc)
            {
                return null;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static List<Operator> GetAllOperators()
        {
            try
            {
                command.CommandText = "sp_GetAllOperators";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                DataTable dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                if (dtMaster != null && dtMaster.Rows.Count > 0)
                {
                    List<Operator> operators = new List<Operator>();

                    foreach (DataRow dr in dtMaster.Rows)
                    {
                        Operator op = new Operator();

                        op.TypeId = Convert.ToInt32(dr["TypeId"]);
                        op.OperatorDisplayName = dr["OperatorDisplayName"].ToString();
                        op.OperatorSchemaName = dr["OperatorSchemaName"].ToString();

                        operators.Add(op);
                    }

                    return operators;

                }

                else
                    return null;
            }
            catch (SqlException sqlExc)
            {
                return null;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static DataTable ExecuteTextQuery(string query)
        {
            try
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                command.Parameters.Clear();

                DataTable dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                if (dtMaster != null && dtMaster.Rows.Count > 0)
                {
                    return dtMaster;
                }
                else
                    return null;
            }
            catch (SqlException sqlExc)
            {
                return null;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        public static Guid CreateView(string viewName, int entityId, List<QueryCondition> conditions)
        {
            try
            {
                Guid viewId = Guid.Empty;
                // Create ViewHeader
                command.CommandText = "sp_InsertSearchView";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("@viewName", viewName));
                command.Parameters.Add(new SqlParameter("@entityId", entityId));
                command.Parameters.Add(new SqlParameter("@viewId", SqlDbType.UniqueIdentifier));
                command.Parameters["@viewId"].Direction = ParameterDirection.Output;

                OpenConnection();

                //Guid viewId = (Guid)command.ExecuteScalar();
                command.ExecuteNonQuery();

                viewId = (Guid)command.Parameters["@viewId"].Value;

                CloseConnection();

                if (conditions != null && conditions.Count > 0)
                    InsertQueryConditions(viewId, conditions);

                return viewId;
            }
            catch (SqlException sqlExc)
            {
                CloseConnection();
                return Guid.Empty;
            }
            catch (Exception exc)
            {
                CloseConnection();
                return Guid.Empty;
            }

        }

        public static bool UpdateView(Guid viewId, string viewName, List<QueryCondition> conditions)
        {
            try
            {
                // Update View Name
                UpdateViewName(viewId, viewName);

                // Delete the old query conditions
                DeleteQueryConditions(viewId);

                // Insert the new query conditions
                InsertQueryConditions(viewId, conditions);

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool DeleteView(Guid viewId)
        {
            try
            {
                // Delete conditions
                DeleteQueryConditions(viewId);

                // Delete View Header
                // Create ViewHeader
                command.CommandText = "sp_DeleteSearchView";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("@viewId", viewId));

                OpenConnection();

                command.ExecuteNonQuery();

                CloseConnection();

                return true;
            }
            catch (SqlException sqlExc)
            {
                CloseConnection();
                return false;
            }
            catch (Exception exc)
            {
                CloseConnection();
                return false;
            }


        }

        private static bool DeleteQueryConditions(Guid viewId)
        {

            command.CommandText = "sp_DeleteQueryConditions";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Clear();
            command.Parameters.Add(new SqlParameter("@viewId", viewId));

            try
            {
                OpenConnection();

                command.ExecuteNonQuery();

                CloseConnection();

                return true;
            }
            catch (SqlException sqlExc)
            {
                CloseConnection();
                return false;
            }
            catch (Exception exc)
            {
                CloseConnection();
                return false;
            }

        }

        private static bool InsertQueryConditions(Guid viewId, List<QueryCondition> conditions)
        {
            try
            {
                foreach (QueryCondition condition in conditions)
                {
                    command.CommandText = "sp_InsertQueryCondition";
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Clear();
                    command.Parameters.Add(new SqlParameter("@viewId", viewId));
                    command.Parameters.Add(new SqlParameter("@fieldId", condition.FieldId));
                    command.Parameters.Add(new SqlParameter("@typeId", condition.FieldType.Id));
                    command.Parameters.Add(new SqlParameter("@operatorschemaName", condition.OperatorSchemaName));
                    command.Parameters.Add(new SqlParameter("@value", GetValueFromQueryCondition(condition)));

                    OpenConnection();

                    command.ExecuteNonQuery();

                    CloseConnection();

                }

                return true;

            }
            catch (SqlException sqlExc)
            {
                return false;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        private static string GetValueFromQueryCondition(QueryCondition condition)
        {
            if (condition.LookupValue != 0) // lookup
                return condition.LookupValue.ToString();
            else if (condition.LookupGuidValue != Guid.Empty) // Main Entity lookup
                return condition.LookupGuidValue.ToString();
            else // other
                return condition.Value;
        }

        public static List<QueryCondition> GetQueryConditions(Guid viewId)
        {
            try
            {
                command.CommandText = "sp_GetQueryConditions";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();
                command.Parameters.Add("@viewId", SqlDbType.UniqueIdentifier).Value = viewId;

                DataTable dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                if (dtMaster != null && dtMaster.Rows.Count > 0)
                {
                    List<QueryCondition> lstConditions = new List<QueryCondition>();

                    foreach (DataRow dr in dtMaster.Rows)
                    {
                        QueryCondition condition = new QueryCondition();
                        condition.FieldId = Convert.ToInt32(dr["FieldId"]);
                        condition.FieldSchemaName = dr["FieldSchemaName"].ToString();
                        condition.FieldDisplayName = dr["FieldDisplayName"].ToString();
                        condition.FieldType = new Item { Value = dr["FieldTypeName"].ToString(), Id = Convert.ToInt32(dr["FieldTypeId"]) };
                        condition.OperatorSchemaName = dr["OperatorSchemaName"].ToString();
                        condition.OperatorDisplayName = dr["OperatorDisplayName"].ToString();

                        if (condition.FieldType.Id == 15) // lookup
                        {
                            Guid tempGuid;
                            int tempInt;

                            if (Guid.TryParse(dr["Value"].ToString(), out tempGuid))
                            {
                                // Guid (Account / Slot / User)
                                condition.LookupGuidValue = tempGuid;
                                condition.Value = dr["LabelValue"].ToString();
                            }
                            else if (int.TryParse(dr["Value"].ToString(), out tempInt))
                            {
                                // Lookup
                                condition.LookupValue = tempInt;
                                condition.Value = dr["LabelValue"].ToString();
                            }
                        }
                        else if (condition.FieldType.Id == 16) // bool
                        {
                            condition.LookupValue = Convert.ToInt32(dr["Value"]);
                            condition.Value = dr["LabelValue"].ToString();
                        }
                        else // other (string - date - numeric)
                            condition.Value = dr["Value"].ToString();

                        lstConditions.Add(condition);
                    }

                    return lstConditions;
                }
                return null;
            }
            catch (SqlException sqlExc)
            {
                return null;
            }
            catch (Exception exc)
            {
                return null;
            }

        }

        public static List<Item> GetSearchViews(int entityId)
        {
            try
            {

                command.CommandText = "sp_GetAllSearchViews";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("@entityId", entityId));

                DataTable dtMaster = new DataTable();

                adapter.Fill(dtMaster);

                if (dtMaster != null && dtMaster.Rows.Count > 0)
                {
                    List<Item> lstViews = new List<Item>();

                    foreach (DataRow dr in dtMaster.Rows)
                    {
                        Item view = new Item();

                        view.GUID = new Guid(dr["viewId"].ToString());
                        view.Value = dr["viewName"].ToString();


                        lstViews.Add(view);
                    }

                    return lstViews;

                }

                else
                    return null;

            }
            catch (SqlException sqlExc)
            {
                return null;
            }
            catch (Exception exc)
            {
                return null;
            }

        }

        public static bool UpdateViewName(Guid viewId, string viewName)
        {
            try
            {
                // Create ViewHeader
                command.CommandText = "sp_UpdateSearchView";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("@viewId", viewId));
                command.Parameters.Add(new SqlParameter("@viewName", viewName));

                OpenConnection();

                command.ExecuteNonQuery();

                CloseConnection();

                return true;
            }
            catch (SqlException sqlExc)
            {
                CloseConnection();
                return false;
            }
            catch (Exception exc)
            {
                CloseConnection();
                return false;
            }
        }
    }
}
